import argparse
import os
import sys
from datetime import datetime, timedelta

# 将项目根目录加入 sys.path，便于独立脚本导入项目内模块
PROJECT_ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
if PROJECT_ROOT not in sys.path:
    sys.path.insert(0, PROJECT_ROOT)

from mysql_tool.mysql_tool import MySQLTool


def fmt_rate(numer, denom):
    if denom and denom > 0:
        return numer / denom
    return None


def main():
    parser = argparse.ArgumentParser(description="查询指定产线在某天某个小时的产出并打印到控制台")
    parser.add_argument("--line_id", type=str, required=True, help="产线编号，可为字符串，如 line2")
    parser.add_argument("--hour", type=int, default=19, help="小时(0-23)，默认19")
    parser.add_argument("--date", type=str, default=None, help="日期 YYYY-MM-DD，默认今天")
    parser.add_argument("--cumulative", action="store_true", help="同时输出到该小时(含)的累计数据")

    args = parser.parse_args()

    if args.hour < 0 or args.hour > 23:
        raise SystemExit("--hour 必须是 0-23 的整数")

    if args.date:
        try:
            day = datetime.strptime(args.date, "%Y-%m-%d").date()
        except ValueError:
            raise SystemExit("--date 格式应为 YYYY-MM-DD")
    else:
        day = datetime.now().date()

    mysql = MySQLTool()

    # 当小时汇总
    sql_sum = (
        """
        SELECT
            COALESCE(SUM(panel_count),0) AS total,
            COALESCE(SUM(board_ok_count),0) AS ok_count,
            COALESCE(SUM(board_ng_count),0) AS ng_count
        FROM smt_aoi_scan_record
        WHERE line_id = %s AND DATE(created_at) = %s AND HOUR(created_at) = %s
        """
    )
    row = mysql.execute_query(sql_sum, (args.line_id, day, args.hour))
    total = float(row[0][0] or 0) if row else 0.0
    ok_count = float(row[0][1] or 0) if row else 0.0
    ng_count = float(row[0][2] or 0) if row else 0.0

    print("=== 小时汇总 ===")
    print(f"line_id={args.line_id} date={day} hour={args.hour:02d}:00-{args.hour:02d}:59")
    print(f"panel_total={total} ok_count={ok_count} ng_count={ng_count}")
    pr = fmt_rate(ok_count, total)
    yr = fmt_rate(total - ng_count, total)  # 良品率 = (panel - NG) / panel
    print(f"pass_rate(OK/panel)={pr if pr is not None else 'N/A'}  yield_rate((panel-NG)/panel)={yr if yr is not None else 'N/A'}")

    # 按工单明细（便于核账）
    sql_by_wo = (
        """
        SELECT
            work_order,
            MAX(model_name) AS model_name,
            COALESCE(SUM(panel_count),0) AS total,
            COALESCE(SUM(board_ok_count),0) AS ok_count,
            COALESCE(SUM(board_ng_count),0) AS ng_count
        FROM smt_aoi_scan_record
        WHERE line_id = %s AND DATE(created_at) = %s AND HOUR(created_at) = %s
        GROUP BY work_order
        ORDER BY total DESC
        """
    )
    rows = mysql.execute_query(sql_by_wo, (args.line_id, day, args.hour))
    print("--- 按工单 ---")
    if rows:
        for r in rows:
            wo = r[0]
            model = r[1]
            t = float(r[2] or 0)
            ok = float(r[3] or 0)
            ng = float(r[4] or 0)
            print(f"work_order={wo} model={model} total={t} ok={ok} ng={ng}")
    else:
        print("(无数据)")

    if args.cumulative:
        # [00:00, hour+1) 的累计窗口
        start_dt = datetime.combine(day, datetime.min.time())
        end_dt = start_dt + timedelta(hours=args.hour + 1)
        # 若 hour=23，end_dt 为次日00:00

        sql_cum = (
            """
            SELECT
                COALESCE(SUM(panel_count),0) AS total,
                COALESCE(SUM(board_ok_count),0) AS ok_count,
                COALESCE(SUM(board_ng_count),0) AS ng_count
            FROM smt_aoi_scan_record
            WHERE line_id = %s AND created_at >= %s AND created_at < %s
            """
        )
        crow = mysql.execute_query(sql_cum, (args.line_id, start_dt, end_dt))
        ctotal = float(crow[0][0] or 0) if crow else 0.0
        cok = float(crow[0][1] or 0) if crow else 0.0
        cng = float(crow[0][2] or 0) if crow else 0.0

        print("=== 累计到该小时(含) ===")
        print(f"panel_total={ctotal} ok_count={cok} ng_count={cng}")
        cpr = fmt_rate(cok, ctotal)
        cyr = fmt_rate(ctotal - cng, ctotal)
        print(f"pass_rate(ΣOK/Σpanel)={cpr if cpr is not None else 'N/A'}  yield_rate((Σpanel-ΣNG)/Σpanel)={cyr if cyr is not None else 'N/A'}")


if __name__ == "__main__":
    main()
